﻿Imports System.Data.SqlClient
Imports extranet.BE
Imports MySql.Data.MySqlClient

Public Class DARecado
  Private objBD As New BancoDeDados
  Public objQuery As New BEQuery

  Sub New()
  End Sub

  Sub New(ByVal booCondicaoSimilar As Boolean)
    objQuery.Where.CondicaoSimilar = booCondicaoSimilar
  End Sub

  Public Sub Cadastra(ByRef objBERecado As BERecado)
    Try
      objBD.duplicaAspasSimples(objBERecado, GetType(BERecado))
      Dim strSQL = "INSERT INTO ext_recados (remetente, destinatario, recado) " & _
                   "VALUES ('" & objBERecado.Remetente.Usuario & "', '" & _
                                 objBERecado.Destinatario.Usuario & "', '" & _
                                 objBERecado.Recado & "');" & _
                   "SELECT LAST_INSERT_ID();"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objBERecado.IdRecado = objComando.ExecuteScalar()
        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DARecado.Cadastra(): " & ex.Message)
    End Try
  End Sub

  Public Sub Atualiza(ByVal objBERecado As BERecado)
    Try
      objBD.duplicaAspasSimples(objBERecado, GetType(BERecado))
      Dim strSQL = "UPDATE ext_telas_sistema " & _
                   "SET remetente='" & objBERecado.Remetente.Usuario & "', " & _
                       "destinatario='" & objBERecado.Destinatario.Usuario & "', " & _
                       "recado='" & objBERecado.Recado & "' " & _
                   "WHERE id_recado=" & objBERecado.IdRecado & ";"
      objBD.InicializarConexao()
      objBD.InicializarTransacao()
      Try
        Dim objComando As New MySqlCommand(strSQL, BancoDeDados.ConnectionInstance, BancoDeDados.TransactionInstance)
        objComando.ExecuteNonQuery()
        objBD.Commit()
      Catch ex As ApplicationException
        objBD.Rollback()
        Throw New ApplicationException("Erro ao executar o comando: " & ex.Message)
      Finally
        objBD.FinalizarConexao()
      End Try
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DARecado.Atualiza(): " & ex.Message)
    End Try
  End Sub

  Public Sub Exclui(ByVal objBERecado As BERecado)
    Try
      objBD.ExecutaNonQuery("DELETE FROM ext_recados WHERE id_recado=" & objBERecado.IdRecado & ";")
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DARecado.Exclui(): " & ex.Message)
    End Try
  End Sub

  Public Function SelecionaRecado(ByVal objBERecado As BERecado) As BERecado
    Try
      objQuery.Where.CondicaoSimilar = False
      Dim objBERecados As List(Of BERecado) = Seleciona(objBERecado)
      If objBERecados.Count > 0 Then
        objBERecado = objBERecados(0)
      Else
        objBERecado = Nothing
      End If
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DARecado.Seleciona(): " & ex.Message)
    End Try
    Return objBERecado
  End Function

  Public Function Seleciona(ByVal objBERecado As BERecado) As List(Of BERecado)
    Dim objBERecados As List(Of BERecado) = New List(Of BERecado)
    Try
      objBD.duplicaAspasSimples(objBERecado, GetType(BERecado))
      objQuery._Select = "SELECT * FROM ext_recados"
      If objBERecado IsNot Nothing Then
        If objBERecado.IdRecado > 0 Then objQuery.Where.AdicionarCondicaoWhere("id_recado", objBERecado.IdRecado, False)
        objQuery.Where.AdicionarCondicaoWhere("remetente", objBERecado.Remetente.Usuario)
        objQuery.Where.AdicionarCondicaoWhere("destinatario", objBERecado.Destinatario.Usuario)
        objQuery.Where.AdicionarCondicaoWhere("recado", objBERecado.Recado)
      End If
      objBERecados = SelecionaRecados()
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DARecado.Seleciona(): " & ex.Message)
    End Try
    Return objBERecados
  End Function

  Private Function SelecionaRecados() As List(Of BERecado)
    Dim objBERecados As List(Of BERecado) = New List(Of BERecado)
    Try
      Using objSQLCommand As MySqlCommand = New MySqlCommand(objQuery.Query, BancoDeDados.ConnectionInstance())
        Using objSQLDReader As MySqlDataReader = objSQLCommand.ExecuteReader()
          While objSQLDReader.Read()
            objBERecados.Add(New BERecado(objSQLDReader("id_recado"), _
                                      objSQLDReader("remetente").ToString(), _
                                      objSQLDReader("destinatario").ToString(), _
                                      objSQLDReader("recado").ToString(), _
                                      objSQLDReader("data")))
          End While
        End Using
      End Using
    Catch ex As ApplicationException
      Throw New ApplicationException("extranet.DA.DAGrupoAcesso.SelecionaRecados(): " & ex.Message)
    End Try
    Return objBERecados
  End Function

End Class
